﻿CREATE PROCEDURE [vantageexport].[s_Repo]
--WITH EXEC AS 'AFC-HOME\SQLServer'
AS

EXEC sp__Context_SecurityOff 1
--EXEC sp__Context_ArchiveOff 1
EXEC sp__Context_WorkFlowOff 1

DECLARE @Repo TABLE(LoanID	int,StatusID	int, StatusDate datetime,StatusUser sysname)
DECLARE @E TABLE(ID1	int,LoanID	int,StatusID	int,StatusDate	datetime,StatusUser	varchar(32))
DECLARE @E2 TABLE(ID1	int,LoanID	int,StatusID	int,StatusDate	datetime,StatusUser	varchar(32))


INSERT INTO @Repo
SELECT CF.LoanID, CS.StatusID,V_LC.CollateralStatusDate,ISNULL(CA_L.sAMAccountName,'SqlServer') --, V_LC.AccountNumber, V_LC.CollateralStatusRef
FROM         Vantage_In.dbo.Target_LoanContract AS V_LC
INNER JOIN usix.v_CustomerFile_Export_10 AS CF ON V_LC.AccountNumber = CF.VantageID
INNER JOIN vantageexport.ts_CollateralStatus CS ON V_LC.CollateralStatusRef = CS.CollateralStatusRef
INNER JOIN vantage.v_CollateralAction_Last CA_L ON CA_L.AccountNumber = V_LC.AccountNumber
WHERE CF.LoanID > 0 
	AND V_LC.CollateralStatusRef<>'Active'
	AND ISNULL(CF.StatusDate_Repo,'1/1/1999') < V_LC.CollateralStatusDate
	AND CF.[Status Code] <> 'P'

--SELECT LoanID, COUNT(*) Count FROM @Repo GROUP BY LoanID HAVING COUNT(*)>1

UPDATE R SET StatusID = Repo.StatusID, StatusUser = Repo.StatusUser,StatusDate = Repo.StatusDate
OUTPUT inserted.ID,inserted.LoanID,inserted.StatusID,inserted.StatusDate,inserted.StatusUser INTO @E
FROM t_Repossession AS R INNER JOIN @Repo Repo ON Repo.LoanID = R.LoanID

--SELECT * FROM @E

DECLARE @Seed int SELECT @Seed = MAX(ID) FROM t_Repossession

DBCC CHECKIDENT (t_Repossession, reseed, @Seed)

INSERT INTO t_Repossession(LoanID,StatusID,StatusUser,StatusDate)
OUTPUT inserted.ID,inserted.LoanID,inserted.StatusID,inserted.StatusDate,inserted.StatusUser  INTO @E2
SELECT DISTINCT Repo.LoanID,Repo.StatusID,Repo.StatusUser,Repo.StatusDate
FROM @Repo Repo
LEFT OUTER JOIN @E E ON Repo.LoanID = E.LoanID
WHERE E.LoanID IS NULL

--SELECT E2.*,S.NameLong FROM @E2 E2
--INNER JOIN v_State S ON E2.StatusID = S.ID

